package DataLayer.DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DataLayer.Model.DelOrdre;
import DataLayer.Model.EkstraService;
import DataLayer.Model.Produkt;
import DataLayer.Model.Service;

public class DBDelOrdre implements IFDBDelOrdre
{
	private Connection con = DBConnection.getInstance().getDBcon();
	private ArrayList<Service> sList;
	private ArrayList<Produkt> pList;

	@Override
	public ArrayList<DelOrdre> getAllDelOrdre(int ordreId)
	{
		return miscWhere("ordreId_fk = '" + ordreId + "'");
	}

	@Override
	public DelOrdre getDelOrdre(int delOrdreId)
	{
		String wClause = "delOrdreId = '" + delOrdreId + "'";
		System.out.println("Search delOrdreId " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public int insertDelOrdre(DelOrdre delo, int ordreId, String ordreType) throws Exception
	{
		int nextID = GetMax.getMaxId("SELECT MAX(delOrdreId) FROM DelOrdre");
		nextID = nextID + 1;
		int rc = -1;
		String query = "";
		boolean isPainting = false;
		if (ordreType.equals("Butik"))
		{
			query = "INSERT INTO DelOrdre(delOrdreId, antal, ordreId_fk, produktId_fk, serviceId_fk)  VALUES('"
					+
					nextID + "','" +
					delo.getAntal() + "','" +
					ordreId + "','" +
					delo.getProdukt().getProduktId() + "',null)";
		}
		if (ordreType.equals("Painting"))
		{
			query = "INSERT INTO DelOrdre(delOrdreId, antal, ordreId_fk, produktId_fk, serviceId_fk)  VALUES('"
					+
					nextID + "','" +
					delo.getAntal() + "','" +
					ordreId + "',null,'" + delo.getService().getServiceId() + "')";
			isPainting = true;
		}
		System.out.println("insert : " + query);
		try
		{ // insert new DelOrdre
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			if (isPainting)
			{
				for (EkstraService es : delo.getEkstraServiceList())
				{
					DBEkstraService dbES = new DBEkstraService();
					dbES.insertEkstraService(es, nextID);
				}
			}
		}// end try
		catch (SQLException ex)
		{
			System.out.println("DelOrdre was not inserted");
			throw new Exception("DelOrdre was not inserted");
		}
		return (rc);
	}

	// @Override
	public int updateDelOrdre(DelOrdre delo, int ordreId, String ordreType)
	{
		int rc = -1;
		String query = "";
		if (ordreType.equals("Butik"))
		{
			query = "UPDATE DelOrdre SET " +
					"antal ='" + delo.getAntal() + "', " +
					"produktId_fk ='" + delo.getProdukt().getProduktId() + "', " +
					"serviceId_fk = null, " +
					"ordreId_fk ='" + ordreId + "'" +
					" WHERE delOrdreId = '" + delo.getDelordreId() + "'";
		}
		if (ordreType.equals("Painting"))
		{
			query = "UPDATE DelOrdre SET " +
					"antal ='" + delo.getAntal() + "', " +
					"produktId_fk = null, " +
					"serviceId_fk ='" + delo.getService().getServiceId() + "', " +
					"ordreId_fk ='" + ordreId + "'" +
					" WHERE delOrdreId = '" + delo.getDelordreId() + "'";
			for (EkstraService es : delo.getEkstraServiceList())
			{
				DBEkstraService dbES = new DBEkstraService();
				dbES.updateEkstraService(es, delo.getDelordreId());
			}
		}
		System.out.println("Update query:" + query);
		try
		{ // update DelOrdre
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex)
		{
			System.out.println("Update exception in DelOrdre db: " + ex);
		}
		return (rc);
	}

	@Override
	public int deleteDelOrdre(DelOrdre delo)
	{
		int rc = -1;
		String query = "DELETE FROM DelOrdre WHERE delOrdreId = '"
				+ delo.getDelordreId() + "'";
		System.out.println(query);
		try
		{ // delete FROM DelOrdre
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex)
		{
			System.out.println("Delete exception in DelOrdre db: " + ex);
		}
		return (rc);
	}

	private ArrayList<DelOrdre> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<DelOrdre> list = new ArrayList<DelOrdre>();
		String query = buildQuery(wClause);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				DelOrdre delo = new DelOrdre();
				delo = buildDelOrdre(results);
				list.add(delo);
			}
			stmt.close();
			cleanLister();
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private DelOrdre singleWhere(String wClause)
	{
		ResultSet results;
		DelOrdre delo = new DelOrdre();
		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next())
			{
				delo = buildDelOrdre(results);
				stmt.close();
				cleanLister();
			}
			else
			{
				delo = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return delo;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT delOrdreId, antal, produktId_fk, serviceId_fk FROM DelOrdre ";
		if (wClause.length() > 0)
		{
			query += "WHERE " + wClause;
		}
		return query;
	}

	/**
	 * builds an object delo of the class DelOrdre
	 * 
	 * @return DelOrdre
	 */
	private DelOrdre buildDelOrdre(ResultSet results)
	{
		DelOrdre delo = new DelOrdre();
		try
		{
			delo.setDelordreId(results.getInt("delOrdreId"));
			delo.setAntal(results.getInt("antal"));
			if (results.getObject("produktId_fk") != null)
			{
				delo.setProdukt(getProdukt(results.getInt("produktId_fk")));
				delo.setService(null);
			}
			else if (results.getObject("serviceId_fk") != null)
			{
				delo.setService(getService(results.getInt("serviceId_fk")));
				delo.setProdukt(null);
				DBEkstraService dbES = new DBEkstraService();
				delo.setEkstraServiceList(dbES.getAllEkstraServicer(results
						.getInt("delOrdreId")));
			}
		}
		catch (Exception e)
		{
			System.out.println("error in building the DelOrdre object");
		}
		return delo;
	}
	
	private Produkt getProdukt(int produktId)
	{
		Produkt tmpProdukt = null;
		if (pList == null)
		{
			pList = new DBProdukt().getAllProdukt();
		}
		for (Produkt p : pList)
		{
			if (p.getProduktId() == produktId)
			{
				tmpProdukt = p;
			}
		}
		return tmpProdukt;
	}
	
	private Service getService(int serviceId)
	{
		Service tmpService = null;
		if (sList == null)
		{
			sList = new DBService().getAllService();
		}
		for (Service s : sList)
		{
			if (s.getServiceId() == serviceId)
			{
				tmpService = s;
			}
		}
		return tmpService;
	}
	
	private void cleanLister()
	{
		sList = null;
		pList = null;
	}
}
